package com.wms.service;

import com.wms.entity.StorageInOut;
import com.mes.entity.User;
import com.yy.base.common.dao.Dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;

@Service
@SuppressWarnings("unchecked")
public class StorageDetailService {
	@Autowired
	private Dao dao;

	/**
	 * 根据物料批次ID，获取出入库货位、库存、仓库信息
	 * @param storageBatchId
	 * @return
	 */
	public List<Map<String, Object>> getStorageBatchProductInfo(String storageBatchId){
		String sql ="select wsio.m_warehouse_id wId ,wsio.m_Locator_Id, wsio.c_uom_id, sum(qty) qty," +
				"	sum(case when status = 'OK' then wsio.qty else 0 end) qtyOk," +
				"	sum(case when status = 'LOCKED' then wsio.qty else 0 end) Qtylock," +
				"	(select value from M_Locator l where l.M_Locator_id = wsio.M_Locator_id) LOCATOR " +
				"  from Wms_Storage_In_Out wsio " +
				" where wsio.status <> 'CANCEL'" +
				"	and Wms_Storage_Batch_ID = ?" +
				" group by wsio.m_Locator_Id, wsio.c_uom_id,wsio.m_warehouse_id ";
		return dao.query(sql, storageBatchId);
	}
	
	
	/**
	 * 根据物料批次，仓库，获取出入库货位、库存、仓库信息
	 * @param batchNo
	 * @return
	 */
	public Map<String, Object> getStorageBatchWarehouseInfo(String batchNo, String warehouseId){
		String sql ="select sum(io.qty) 	qty,io.m_product_id	,ml.value,sb.wms_storage_batch_id,io.m_locator_id	"+
		        "   from wms_storage_batch sb, wms_storage_in_out io left join m_locator ml		" +
		        "	on io.m_locator_id = ml.m_locator_id	"+
		        "  where sb.wms_storage_batch_id = io.wms_storage_batch_id	"+
		        "    and batch_no = ?		"+
		        "    and io.m_warehouse_id = ? " +
		        "	group by io.m_product_id,ml.value,sb.wms_storage_batch_id,io.m_locator_id ";
		return dao.queryUnique(sql, batchNo,warehouseId);
	}
	
	
	/**
	 * 根据出入库ID，获取出库记录信息
	 * @return
	 */
	public Map<String, Object> getStorageInOutInfo(String inoutId) {
		String sql ="select m_warehouse_id, M_locator_id, qty, data_id, c_Uom_Id, Wms_Storage_Batch_Id " +
				"from Wms_Storage_In_Out io where Wms_Storage_In_Out_id = ?";
		return dao.queryUnique(sql, inoutId);
	}
	/**
	 * 根据货位编码，或货位ID获取货位、仓库ID
	 * @param value
	 * @return
	 */
	public Map<String, Object> getLocatorIdByLocator(String value){
		String sql ="select m_warehouse_id, M_locator_id LOCATORID from M_locator where value = ? or M_locator_Id = ?";
		return dao.queryUnique(sql, value, value);
	}
	
	/**
	 * 增加入库记录
	 * @param sio
	 * @param user
	 * @return
	 */
	public int addInStorage(StorageInOut sio, User user) {
		if (sio.getQty() == 0) {
			return 0;
		}
		String sql = "insert into Wms_Storage_In_Out(WMS_STORAGE_IN_OUT_ID, WMS_STORAGE_BATCH_ID, " +
				"m_warehouse_id, M_LOCATOR_ID, M_Product_Id, Qty, c_uom_id, status, description, " +
				"data_Id, opt_code, opt_mode, main_Type, sub_type, org_type," +
				"AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, UPDATEDBY)" +
				" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		return dao.executeSql(sql, sio.getId(), sio.getStorageBatchId(), sio.getWarehouseId(), sio.getLocatorId(), 
				sio.getProductId(), sio.getQty() > 0 ? sio.getQty() : -sio.getQty(), sio.getUomId(), 
				sio.getStatus() == null || "".equals(sio.getStatus()) ? "OK" : sio.getStatus(), sio.getDescription(), 
				sio.getDataId(), sio.getOptCode(), sio.getOptMode(), sio.getMainType(), sio.getSubType(), sio.getSaleType(),
				user.getClientId(), user.getOrgId(), user.getId(), user.getId());
	}
	/**
	 * 增加入库记录
	 * @param sio
	 * @param user
	 * @return
	 */
	public int addInStorage1(StorageInOut sio, User user) {
		if (sio.getQty() == 0) {
			return 0;
		}
		String sql = "insert into Wms_Storage_In_Out_qc(WMS_STORAGE_IN_OUT_ID, WMS_STORAGE_BATCH_ID, " +
				"m_warehouse_id, M_LOCATOR_ID, M_Product_Id, Qty, c_uom_id, status, description, " +
				"data_Id, opt_code, opt_mode, main_Type, sub_type, org_type," +
				"AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, UPDATEDBY)" +
				" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		return dao.executeSql(sql, sio.getId(), sio.getStorageBatchId(), sio.getWarehouseId(), sio.getLocatorId(), 
				sio.getProductId(), sio.getQty() > 0 ? sio.getQty() : -sio.getQty(), sio.getUomId(), 
				sio.getStatus() == null || "".equals(sio.getStatus()) ? "OK" : sio.getStatus(), sio.getDescription(), 
				sio.getDataId(), sio.getOptCode(), sio.getOptMode(), sio.getMainType(), sio.getSubType(), sio.getSaleType(),
				user.getClientId(), user.getOrgId(), user.getId(), user.getId());
	}
	/**
	 * 增加出库记录
	 * @param sio
	 * @param user
	 * @return
	 */
	public int addOutStorage(StorageInOut sio, User user) {
		if (sio.getQty() == 0) {
			return 0;
		}
		String sql = "insert into Wms_Storage_In_Out(WMS_STORAGE_IN_OUT_ID, WMS_STORAGE_BATCH_ID, " +
				"m_warehouse_id, M_LOCATOR_ID, M_Product_Id, QTY, c_uom_id, status, description, " +
				"data_Id, opt_code, opt_mode, main_Type, sub_type, org_type," +
				"AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, UPDATEDBY)" +
				" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		return dao.executeSql(sql, sio.getId(), sio.getStorageBatchId(), sio.getWarehouseId(), sio.getLocatorId(), 
				sio.getProductId(), sio.getQty() < 0 ? sio.getQty() : -sio.getQty(), sio.getUomId(), 
				sio.getStatus() == null || "".equals(sio.getStatus()) ? "OK" : sio.getStatus(), sio.getDescription(), 
				sio.getDataId(), sio.getOptCode(), sio.getOptMode(), sio.getMainType(), sio.getSubType(), sio.getSaleType(),
				user.getClientId(), user.getOrgId(), user.getId(), user.getId());
	}
	
	/**
	 * 根据货位或物料id，或批次号，获取出入库信息
	 * @param locatorId
	 * @return
	 */
	public List<Map<String, Object>> getStorageInOutByLocatorId(String locatorId){
		//只包含非空的值
		String sql ="select Wms_Storage_In_Out_ID id, Wms_Storage_Batch_ID, Qty, Qty_Out " +
				"from Wms_Storage_In_Out where M_Locator_ID=?";
		List<Map<String, Object>> list = dao.query(sql, locatorId);
		
		if (list.isEmpty()) {
			sql="select wsio.Wms_Storage_In_Out_ID id, wsio.Wms_Storage_Batch_ID, wsio.Qty, wsio.Qty_Out " +
				"  from wms_storage_batch wsb,Wms_Storage_In_Out wsio " +
				" where wsb.wms_storage_batch_id = wsio.wms_storage_batch_id " +
				"	and batch_no=?";
			list = dao.query(sql, locatorId);
		}
		if (list.isEmpty()) {
			sql = "select wsio.Wms_Storage_In_Out_ID id, wsio.Wms_Storage_Batch_ID,wsio.Qty,wsio.Qty_Out " +
					"  from wms_storage_batch wsb,Wms_Storage_In_Out wsio " +
					" where wsb.wms_storage_batch_id = wsio.wms_storage_batch_id " +
					"	and m_product_id=?";
			list = dao.query(sql, locatorId);
		}
		return list;
	}
	/**
	 * 修改出库记录里锁定状态为OK
	 * @param dataId
	 * @param user
	 * @return
	 */
	public int updateLockStorageToOk(String dataId, User user) {
		String sql = "update wms_storage_in_out set status = 'OK', updatedby = ?, updated = now() " +
				"where Status = 'LOCKED' and data_id = ?";
		return dao.executeSql(sql, user.getId(), dataId);
	}
	/***
	 * 根据出库记录id修改批次   DATA_ID
	 * @param dataId  出库记录Id
	 * @param batchNo	批次号
	 * @param locator	用户
	 * @return
	 */
	public int updateLockStorageBatchOk(String dataId, String locator, String batchNo) {
		String sql = "update wms_storage_in_out set wms_storage_batch_id = (select wms_storage_batch_id from wms_storage_batch where batch_no = ?)," +
				"	m_locator_id = (select	m_locator_id from m_locator where value = ?),status = 'OK'	"+
				"	where data_id = ?";
		return dao.executeSql(sql, batchNo,locator, dataId);
	}
	/***
	 * 根据出库记录id修改锁定数量   DATA_ID
	 * @param dataId  出库记录Id
	 * @param qty	数量
	 * @return
	 */
	public int updateLockStorageBatchOk(String dataId, double qty) {
		String sql = "update wms_storage_in_out set status = 'OK', qty =?	"+
				"	where wms_storage_in_out_id = ?";
		return dao.executeSql(sql, qty,dataId);
	}
	
	/**
	 * 根据批次号查询出入库记录
	 * @param batchNo
	 * @return
	 */
	public List<Map<String, Object>> findStorage(String batchNo) {
		String sql = "select io.m_product_id,		"+
			"	       io.wms_storage_batch_id,		"+
			"	       io.m_warehouse_id,		"+
			"	       io.m_locator_id,		"+
			"	       io.c_uom_id,		"+
			"	       io.opt_code,		"+
			"	       io.opt_mode,		"+
			"	       io.main_type,		"+
			"	       io.sub_type,		"+
			"	       io.org_type		"+
			"	  from wms_storage_batch sb, wms_storage_in_out io		"+
			"	 where sb.wms_storage_batch_id = io.wms_storage_batch_id		"+
			"	   and sb.batch_no = ?		"+
			"	    or sb.BOX_PACKAGE_CODE = ?		"+
			"	    or sb.BIG_PACKAGE_CODE = ?";
		return dao.query(sql, batchNo,batchNo,batchNo);
		
	}
	
	/**
	 * 根据批次号查询出入库记录
	 * @param batchNo
	 * @return
	 */
	public List<Map<String, Object>> findStorageHourse(String batchNo) {
		String sql = "select c.m_product_id, "
				+ "    c.qty, "
				+ "    c.wms_storage_batch_id, "
				+ "    c.m_warehouse_id, "
				+ "    c.m_locator_id, "
				+ "    c.c_uom_id ,"
				+ "    c.opt_code, "
				+ "    c.opt_mode, "
				+ "    c.main_type, "
				+ "    c.sub_type, "
				+ "    c.org_type "
				+ "    from wms_storage_in_out c "
				+ "    where c.wms_storage_batch_id in "
				+ "    (select wms_storage_batch_id "
				+ "     from wms_storage_batch a "
				+ "      where a.batch_no = ? "
				+ "       or a.box_package_code = ? "
				+ "     or a.big_package_code = ?) "
				+ "     and c.qty >0               ";
			//  + "    and (c.opt_mode = '成品入库' or  c.opt_mode = '成品期初入库') ";
		return dao.query(sql, batchNo,batchNo,batchNo);
		
	}
	/**
	 * 根据出库记录Id查询出入库记录
	 * @param inOutId
	 * @return
	 */
	public List<Map<String, Object>> findStorageInOut(String inOutId) {
		String sql = "select io.m_product_id,		"+
			"	       io.wms_storage_batch_id,		"+
			"	       io.m_warehouse_id,		"+
			"	       io.m_locator_id,		"+
			"	       io.c_uom_id,		"+
			"	       io.opt_code,		"+
			"	       io.opt_mode,		"+
			"	       io.main_type,		"+
			"	       io.sub_type,		"+
			"	       io.org_type		"+
			"	  from wms_storage_batch sb, wms_storage_in_out io		"+
			"	 where sb.wms_storage_batch_id = io.wms_storage_batch_id		"+
			"	   and io.wms_storage_in_out_id = ?		";
		return dao.query(sql, inOutId);
	}
}
